package com.zxl.study.support;


import com.alibaba.druid.pool.DruidPooledConnection;

import com.zxl.study.entities.MetaDatabase;
import com.zxl.study.entities.MetaTable;
import io.micrometer.core.instrument.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.Date;
import java.util.*;

@Slf4j
public class PostgresStrategy implements Strategy<DruidPooledConnection> {

    /**
     * 注册数据库驱动程序
     *
     * @param
     * @param
     * @return void
     */
    @Override
    public void setDriver() {
        try {
            //注册数据库驱动程序
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            log.error("未找到驱动类: {}", e.getMessage());
        }
    }

    @Override
    public List<MetaTable> syncGetRemoteTables(Connection conn, MetaDatabase database, boolean isFilter, boolean isComplete) {
        List<MetaTable> tablesList = new ArrayList<>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT a.table_schema, a.table_name, a.table_type, b.comments");
            sql.append(" FROM information_schema.tables a");
            sql.append(" INNER JOIN (");
            sql.append("  SELECT nspname, relname, col_description(pg_class.oid, 0) AS comments");
            sql.append("  FROM pg_class");
            sql.append("  INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace");
            sql.append(" ) b ON table_schema || '.' || a.table_name = b.nspname || '.' || b.relname");
            sql.append(" WHERE a.table_schema NOT IN ('pg_catalog', 'information_schema')");
            sql.append(" AND a.table_type NOT IN ('FOREIGN TABLE')");
            sql.append(" AND a.table_schema || '.' || a.table_name NOT IN (");
            sql.append("  SELECT nmsp_child.nspname || '.' || child.relname AS child");
            sql.append("  FROM pg_inherits");
            sql.append("  JOIN pg_class parent ON pg_inherits.inhparent = parent.oid");
            sql.append("  JOIN pg_class child ON pg_inherits.inhrelid = child.oid");
            sql.append("  JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace");
            sql.append("  JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace");
            sql.append(" )");
            if (isFilter) {
                //根据管理表过滤
                if (StringUtils.isNotBlank(database.getManagedTables())) {
                    String managedTables = database.getManagedTables().replace(",", "','");
                    sql.append(" AND (a.table_schema || '.' || a.table_name) IN ('").append(managedTables).append("')");
                }
            }

            //根据模式名过滤
            String schemaName = database.getSchemaName();
            if (StringUtils.isNotBlank(schemaName)) {
                if (schemaName.contains(",")) {
                    schemaName = schemaName.replace(",", "','");
                }
                sql.append(" AND a.table_schema IN ('").append(schemaName).append("')");
            }
            sql.append("ORDER BY a.table_schema, a.table_name");

            rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                MetaTable table = new MetaTable();
                table.setSchemaName(rs.getString("table_schema"));
                table.setName(rs.getString("table_name"));
                if (StringUtils.isNotBlank(rs.getString("comments"))) {
                    table.setNameAlias(rs.getString("comments"));
                }
                if (isComplete) {
                    //补全信息，供维护时使用
                    table.setDatabaseId(database.getId());
                    table.setCreatePerson(database.getCreatePerson());
                    table.setCreateTime(new Date());
                    table.setUpdatePerson(database.getUpdatePerson());
                    table.setUpdateTime(new Date());
                    table.setIsDeleted(Constants.IS_DELETED_FALSE);
                }
                tablesList.add(table);
            }
        } catch (Exception e) {
            log.error("错误输出：{}", e.getMessage());
        } finally {
            //关闭连接
            JdbcUtil.close(conn, stmt, rs);
        }
        return tablesList;
    }



}